/****
	Copyright 2011 Taras Kulbachka

	Licensed under the Apache License, Version 2.0 (the "License");
	you may not use this file except in compliance with the License.
	You may obtain a copy of the License at

		http://www.apache.org/licenses/LICENSE-2.0

	Unless required by applicable law or agreed to in writing, software
	distributed under the License is distributed on an "AS IS" BASIS,
	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
	See the License for the specific language governing permissions and
	limitations under the License.
*/
package com.atdevsoft.apps.abyss;

import java.io.File;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.os.Environment;
import android.util.Log;

public class DatabaseHelper {
	private static final int OPEN_RETRIES = 5;
	
	public static final String DATABASE_NAME = "abyss.db";
	private static final int DATABASE_VERSION = 4;
	
	public static final String INDEX_NAME_QUOTES_ID = "source_id_index";
	public static final String TABLE_NAME_ABYSSBESTQUOTES = "quotes";	
	
	public static final String INDEX_NAME_FAVS_ID = "source_id_favs_index";
	public static final String TABLE_NAME_FAVORITES = "favorites";		
	
	public static final String QUOTES_ID = "_id";
	public static final String QUOTES_SOURCE_ID = "bash_id";
	public static final String QUOTES_POSTED = "posted";
	public static final String QUOTES_QUOTE = "quote";	
	public static final String QUOTES_ADDED_TO_FAVS = "quote_added_to_favs";
	
	private static SQLiteDatabase mDb = null;
	
	private static Object lockObject = new Object();
	
	public static File getDefaultWorkingDir() {
		return new File(Environment.getExternalStorageDirectory().getAbsolutePath() + "/abyss/");
	}
	
	private static void initWritableDatabase()
	{		
		if (!Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED)) {			
			return;
		}
		int retries = 0;
		while ((mDb == null) && (retries < OPEN_RETRIES))
		{
			try
			{				
				File sdDir = getDefaultWorkingDir();
				sdDir.mkdirs();
				Log.d("abyss db", sdDir.getAbsolutePath());
				File dbFile = new File(sdDir, "data.sqlite");
				Log.d("abyss db", dbFile.getAbsolutePath());			

				mDb = SQLiteDatabase.openOrCreateDatabase(dbFile, null);
				mDb.setLockingEnabled(false);
				create(mDb);
				upgrade(mDb, mDb.getVersion(), DATABASE_VERSION);				
			}
			catch(SQLiteException e)
			{
				try {					
					Thread.sleep(200);
				} catch (InterruptedException e1) {
				}
			}	
			retries++;
		}				
	}
	
	public static boolean checkDatabaseAvailable() {
		synchronized (lockObject) {
			if (mDb == null)
				initWritableDatabase();
			return (mDb != null);
		}		
	}
	
	public static void databaseInsert(String table, String nullColumnHack, ContentValues values) {
		synchronized (lockObject) {
			if (mDb == null)
				initWritableDatabase();
			if (mDb == null)
				return;
			mDb.insert(table, nullColumnHack, values);
		}
	}
	
	public static void execSQL(String sql) {
		synchronized (lockObject) {
			if (mDb == null)
				initWritableDatabase();
			if (mDb == null)
				return;
			mDb.execSQL(sql);
		}
	}
	
	public static Cursor rawQuery(String sql, String[] selectionArgs) {
		synchronized (lockObject) {
			if (mDb == null)
				initWritableDatabase();
			if (mDb == null)
				return null;
			Cursor res = mDb.rawQuery(sql, selectionArgs);			
			return res;
		}
	}		
	
	public static void beginTransaction() {
		synchronized (lockObject) {
			if (mDb == null)
				initWritableDatabase();
			if ((mDb == null) || (mDb.inTransaction()))
				return;
			mDb.beginTransaction();
		}
	}
	
	public static void setTransactionSuccessful() {
		synchronized (lockObject) {
			if ((mDb == null) || (!mDb.inTransaction()))
				return;
			mDb.setTransactionSuccessful();
		}
	}		
	
	public static void endTransaction() {
		synchronized (lockObject) {
			if ((mDb == null) || (!mDb.inTransaction()))
				return;
			mDb.endTransaction();
		}
	}		
	
	public static void closeDatabase() {
		synchronized (lockObject) {
			if (mDb == null)
				return;
			mDb.close();
			mDb = null;
		}
	}	
	
	private static void create(SQLiteDatabase db) {
		db.execSQL(quotesCreate());		
	}

	private static void upgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (newVersion == oldVersion)
			return;
		
		if (oldVersion < 2) {
			// we will create a new table for each source instead of type field as it should be faster option
			StringBuilder result = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
			result.append(TABLE_NAME_FAVORITES);
			result.append(" (");
			result.append(QUOTES_ID);
			result.append(" INTEGER PRIMARY KEY, ");
			result.append(QUOTES_SOURCE_ID);
			result.append(" INTEGER, ");		
			result.append(QUOTES_POSTED);
			result.append(" TEXT, ");
			result.append(QUOTES_QUOTE);
			result.append(" TEXT);");
			db.execSQL(result.toString());			
		} 
		if (oldVersion < 3) {
			StringBuilder idx = new StringBuilder("CREATE INDEX IF NOT EXISTS ");
			idx.append(INDEX_NAME_QUOTES_ID);
			idx.append(" ON ");
			idx.append(TABLE_NAME_ABYSSBESTQUOTES);
			idx.append(" (");
			idx.append(QUOTES_SOURCE_ID);
			idx.append(")");
			db.execSQL(idx.toString());	
		}

		if (oldVersion < 4) {
			StringBuilder idx = new StringBuilder("CREATE INDEX IF NOT EXISTS ");
			idx.append(INDEX_NAME_FAVS_ID);
			idx.append(" ON ");
			idx.append(TABLE_NAME_FAVORITES);
			idx.append(" (");
			idx.append(QUOTES_SOURCE_ID);
			idx.append(")");
			db.execSQL(idx.toString());
			
			StringBuilder result = new StringBuilder("ALTER TABLE ");
			result.append(TABLE_NAME_ABYSSBESTQUOTES);
			result.append(" ADD COLUMN ");
			result.append(QUOTES_ADDED_TO_FAVS);
			result.append(" BOOLEAN;");			
			db.execSQL(result.toString());	
			
			result = new StringBuilder("ALTER TABLE ");
			result.append(TABLE_NAME_FAVORITES);
			result.append(" ADD COLUMN ");
			result.append(QUOTES_ADDED_TO_FAVS);
			result.append(" BOOLEAN DEFAULT 1;");			
			db.execSQL(result.toString());
		}
		
		db.setVersion(DATABASE_VERSION);
		
	}
	
	private static String quotesCreate()
	{
		StringBuilder result = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
		result.append(TABLE_NAME_ABYSSBESTQUOTES);
		result.append(" (");
		result.append(QUOTES_ID);
		result.append(" INTEGER PRIMARY KEY, ");
		result.append(QUOTES_SOURCE_ID);
		result.append(" INTEGER, ");		
		result.append(QUOTES_POSTED);
		result.append(" TEXT, ");
		result.append(QUOTES_QUOTE);
		result.append(" TEXT);");
		return result.toString();
	}
}
